package com.example.aaa_viewpager.service;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.example.aaa_viewpager.fragment2;
import com.example.aaa_viewpager.entity.User;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.support.v4.app.Fragment;
import android.util.Log;

public class UserDAO {
	private static String DB_NAME = null;
	private static final String TAG = "SQLITE";
	private static String sql = null;
	private Context ctx;

	public UserDAO(Context ctx) {
		super();
		this.ctx = ctx;
	}

	public UserDAO() {
		// TODO Auto-generated constructor stub
	}

	// copy Database
	private void copyDatabase(File dbFile) throws IOException {
		InputStream is = ctx.getAssets().open(DB_NAME);
		OutputStream os = new FileOutputStream(dbFile);

		byte[] buffer = new byte[1024];
		while (is.read(buffer) > 0) {
			os.write(buffer);
		}

		os.flush();
		os.close();
		is.close();
	}

	// 開啟資料庫可讀取
	public SQLiteDatabase openReadDatabase() {
		File dbFile = ctx.getDatabasePath(DB_NAME);

		if (!dbFile.exists()) {
			try {
				File parentDir = new File(dbFile.getParent());
				if (!parentDir.exists()) {
					parentDir.mkdir();
				}
				copyDatabase(dbFile);
			} catch (IOException e) {
				throw new RuntimeException("Error creating source database", e);
			}
		}

		return SQLiteDatabase.openDatabase(dbFile.getPath(), null,
				SQLiteDatabase.OPEN_READONLY);
	}

	// 開啟資料庫可寫入
	public SQLiteDatabase openWriteDatabase() {
		File dbFile = ctx.getDatabasePath(DB_NAME);

		if (!dbFile.exists()) {
			try {
				File parentDir = new File(dbFile.getParent());
				if (!parentDir.exists()) {
					parentDir.mkdir();
				}
				copyDatabase(dbFile);
			} catch (IOException e) {
				throw new RuntimeException("Error creating source database", e);
			}
		}

		return SQLiteDatabase.openDatabase(dbFile.getPath(), null,
				SQLiteDatabase.OPEN_READWRITE);
	}

	// 讀取資料庫內容放到ArrayList
	public List<User> getAll(int position) {
		List<User> ret = new ArrayList<User>();
		switch (position) {
		case 0:
			DB_NAME = "north.db";
			sql = "SELECT * FROM N;";
			break;
		case 1:
			DB_NAME = "center.db";
			sql = "SELECT * FROM C;";
			break;
		case 2:
			DB_NAME = "south.db";
			sql = "SELECT * FROM S;";
			break;
		case 3:
			DB_NAME = "east.db";
			sql = "SELECT * FROM E;";
		}
		SQLiteDatabase db = this.openReadDatabase();

		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(0);
				String name = cursor.getString(1);
				String address = cursor.getString(2);

				String phone = cursor.getString(3);
				int price_1 = cursor.getInt(4);
				int price_2 = cursor.getInt(5);
				int price_3 = cursor.getInt(6);
				byte[] picture = cursor.getBlob(7);
				String geo = cursor.getString(8);

				User u = new User(id, name, address, picture);
				ret.add(u);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return ret;
	}

	// 讀取資料庫內容放到ListView(baseAdapter)
	public ArrayList<HashMap> getAll() {
		ArrayList<HashMap> ret = new ArrayList<HashMap>();
		SQLiteDatabase db = this.openReadDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		HashMap item = null;
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(0);
				String name = cursor.getString(1);
				String address = cursor.getString(2);
				byte[] picture = cursor.getBlob(7);
				item = new HashMap();
				item.put("name", name);
				item.put("address", address);
				item.put("picture", picture);
				ret.add(item);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return ret;
	}
	// 從N_activty輸入的關鍵字，使用此方法取得相似民宿名稱的list
		public ArrayList<HashMap> searchResult(String name, int position) {
			ArrayList<HashMap> ret = new ArrayList<HashMap>();
			switch (position) {
			case 0:
				DB_NAME = "north.db";
				sql = "SELECT * FROM  N  WHERE NAME LIKE " + "'" + "%" + name + "%" + "'" + ";";
				break;
			case 1:
				DB_NAME = "center.db";
				sql = "SELECT * FROM  C  WHERE NAME LIKE " + "'" + "%" + name + "%" + "'" + ";";
				break;
			case 2:
				DB_NAME = "south.db";
				sql = "SELECT * FROM  S  WHERE NAME LIKE " + "'" + "%" + name + "%" + "'" + ";";
				break;
			case 3:
				DB_NAME = "east.db";
				sql = "SELECT * FROM  E  WHERE NAME LIKE " + "'" + "%" + name + "%" + "'" + ";";
			}
			SQLiteDatabase db = this.openReadDatabase();
			// String sql = "SELECT * FROM  N  WHERE NAME = " + "'" + name + "'"+
			// ";";
			Cursor cursor = db.rawQuery(sql, null);
			HashMap item = null;
			if (cursor.moveToFirst()) {
				do{
					int id = cursor.getInt(0);
					name = cursor.getString(1);
					String address = cursor.getString(2);
					byte[] picture = cursor.getBlob(7);
					item = new HashMap();
					item.put("name", name);
					item.put("address", address);
					item.put("picture", picture);
					ret.add(item);
				}while (cursor.moveToNext());
			} 
			
			cursor.close();
			db.close();
			return ret;
		}


	// SQlite取得產品圖片 BB_details.java使用
	public Bitmap getBmp(int selectedIndex) {
		SQLiteDatabase db = this.openReadDatabase();

		Cursor cursor = db.rawQuery(sql, null);
		Bitmap bmpout = null;
		if (cursor.moveToFirst()) {
			cursor.moveToPosition(selectedIndex);
			byte[] image = cursor.getBlob(7);
			bmpout = BitmapFactory.decodeByteArray(image, 0, image.length);
		}
		cursor.close();
		db.close();

		return bmpout;

	}

	// SQlite取得產品圖片 fragment3使用
	public byte[] getPicByte(int position) {

		SQLiteDatabase db = this.openReadDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		// byte[] picture = null;
		if (cursor.moveToFirst()) 
		{
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("PICTURE");
			
		byte[] picture = cursor.getBlob(nameColumnIndex);

		
		cursor.close();
		db.close();

		return picture;

	}

	// 取得民宿name
	public String getName(int position) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) 
		{
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("NAME");
		String name = cursor.getString(nameColumnIndex);

		cursor.close();
		db.close();

		return name;
	}

	// 取得民宿地址
	public String getAddress(int position) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("ADDRESS");
		String address = cursor.getString(nameColumnIndex);

		cursor.close();
		db.close();

		return address;
	}

	// 取得民宿電話
	public String getPhone(int position) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("PHONE");
		String phone = cursor.getString(nameColumnIndex);

		cursor.close();
		db.close();

		return phone;
	}

	// 取得定價
	public int getPrice_1(int position) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("PRICE_1");
		int price_1 = cursor.getInt(nameColumnIndex);

		cursor.close();
		db.close();

		return price_1;
	}

	// 取得假日價
	public int getPrice_2(int position) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("PRICE_2");
		int price_2 = cursor.getInt(nameColumnIndex);

		cursor.close();
		db.close();

		return price_2;
	}

	// 取得平日價
	public int getPrice_3(int position) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("PRICE_3");
		int price_3 = cursor.getInt(nameColumnIndex);

		cursor.close();
		db.close();

		return price_3;
	}
	//取得民宿經緯度
	public String getGeo(int position) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			cursor.moveToPosition(position);
		}
		int nameColumnIndex = cursor.getColumnIndex("GEO");
		String geo = cursor.getString(nameColumnIndex);

		cursor.close();
		db.close();

		return geo;
	}

	// 新增
	public boolean insert(User u) {
		SQLiteDatabase db = this.openWriteDatabase();
		try {
			long uid = db.insert("User", null, u.toContentValues());
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	// 刪除
	public boolean delete(int id) {
		SQLiteDatabase db = this.openWriteDatabase();
		try {
			db.delete("USER", "_id=?", new String[] { id + "" });
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	public User getUserById(int id) {
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM N WHERE ID = " + id + ";";
		Cursor cursor = db.rawQuery(sql, null);
		User ret = null;
		if (cursor.moveToFirst()) {
			id = cursor.getInt(0);
			String name = cursor.getString(1);
			String address = cursor.getString(2);
			byte[] picture = cursor.getBlob(7);
			ret = new User(id, name, address, picture);
		}
		return ret;
	}

	// 從得到的民宿名稱.. 取得對應的id
	public User getIdByName(String name, int position) {
		switch (position) {
		case 0:
			DB_NAME = "north.db";
			sql = "SELECT * FROM  N  WHERE NAME = " + "'" + name + "'" + ";";
			break;
		case 1:
			DB_NAME = "center.db";
			sql = "SELECT * FROM  C  WHERE NAME = " + "'" + name + "'" + ";";
			break;
		case 2:
			DB_NAME = "south.db";
			sql = "SELECT * FROM  S  WHERE NAME = " + "'" + name + "'" + ";";
			break;
		case 3:
			DB_NAME = "east.db";
			sql = "SELECT * FROM  E  WHERE NAME = " + "'" + name + "'" + ";";
		}
		SQLiteDatabase db = this.openReadDatabase();
		// String sql = "SELECT * FROM  N  WHERE NAME = " + "'" + name + "'"+
		// ";";
		Cursor cursor = db.rawQuery(sql, null);
		User ret = null;
		if (cursor.moveToFirst()) {
			int id = cursor.getInt(0);
			name = cursor.getString(1);
			String address = cursor.getString(2);
			String phone = cursor.getString(3);
			int price_1 = cursor.getInt(4);
			int price_2 = cursor.getInt(5);
			int price_3 = cursor.getInt(6);
			byte[] picture = cursor.getBlob(7);

			ret = new User(id, name, address, phone, price_1, price_2, price_3,
					picture);

		}
		return ret;
	}

	// cursor simpleCusorAdapter使用
	public Cursor getAllNotesCursor() {

		// String selectQuery = "SELECT * FROM N;";
		SQLiteDatabase db = this.openReadDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		return cursor;
	}

}
